/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.pdx.cs.guavadata.data;

import com.sun.rowset.FilteredRowSetImpl;
import edu.pdx.cs.guavadata.DBConnection;
import edu.pdx.cs.guavadata.Transaction;
import edu.pdx.cs.guavadata.domain.Domain;
import edu.pdx.cs.guavadata.domain.Enumerated;
import edu.pdx.cs.guavadata.query.ForeignKey;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Dictionary;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.FilteredRowSet;

/**
 *
 * @author VuTran
 */
public class Schema {
    public List<Scheme> tables;

    public Schema() {
        this.tables = new ArrayList<Scheme>();
    }

    public static Schema SchemaFromDB() {
        ResultSet tableColumnInfo = DBConnection.executeQuery("select * from information_schema.columns order by table_name");
        ResultSet keyRows = null;
        ResultSet fkRows = null;
        Map<String, List<Column>> tableColumnMap = new HashMap<String, List<Column>>();
        Map<String, List<Column>> tablePrimaryKeyMap = new HashMap<String, List<Column>>();
        Map<String, List<Column>> tableForeignKeyMap = new HashMap<String, List<Column>>();
        Map<String, String> parent = new HashMap<String, String>();
        Map<String, List<String>> children = new HashMap<String, List<String>>();
        Schema sch = new Schema();
        try {
            while (tableColumnInfo.next()) {
                // add table-column mapping info
                String tableName = tableColumnInfo.getString("Table_Name");
                Column col = new Column(tableColumnInfo.getString("Column_Name"), Domain.fromDBInfoRow(tableColumnInfo));
                if (tableColumnMap.containsKey(tableName)) {
                    tableColumnMap.get(tableName).add(col);
                } else {
                    List<Column> cols = new ArrayList<Column>();
                    cols.add(col);
                    tableColumnMap.put(tableName, cols);
                }

                keyRows = DBConnection.executeQuery("select * from information_schema.key_column_usage order by table_name" +
                        " WHERE Table_name = '" + tableName + "' and Column_Name = '" + tableColumnInfo.getString("Column_Name") + "'");
                if (keyRows.next()) {
                    fkRows = DBConnection.executeQuery("select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS" +
                            " WHERE Constraint_Name = '" + keyRows.getString("Constraint_Name") + "'");
                    if (fkRows.next()) {
                        ResultSet parentKeyRow = DBConnection.executeQuery("select * from information_schema.key_column_usage order by table_name" +
                                " WHERE Constraint+Name = '" + fkRows.getString("Constraint_Name") + "'");
                        parentKeyRow.next();
                        String parentTable = parentKeyRow.getString("Table_Name");
                        if (!parent.containsKey(tableName)) {
                            parent.put(tableName, parentTable);
                        }
                        if (children.containsKey(parentTable)) {
                            children.get(parentTable).add(tableName);
                        } else {
                            List<String> cList = new ArrayList<String>();
                            cList.add(tableName);
                            children.put(parentTable, cList);
                        }
                    } else {
                        if (tablePrimaryKeyMap.containsKey(tableName)) {
                            tablePrimaryKeyMap.get(tableName).add(col);
                        } else {
                            List<Column> cols = new ArrayList<Column>();
                            cols.add(col);
                            tablePrimaryKeyMap.put(tableName, cols);
                        }
                    }

                }
            }

            Map<String, Scheme> tableSchemeMap = new HashMap<String, Scheme>();
            for (String key : tableColumnMap.keySet()) {
                Scheme scheme = sch.newScheme(key, null);
                if (children.containsKey(key))
                    scheme.children = children.get(key);
                scheme.columns = tableColumnMap.get(key);
                if (tablePrimaryKeyMap.containsKey(key))
                    scheme.pkColumns = tablePrimaryKeyMap.get(key);
                if (tableForeignKeyMap.containsKey(key))
                    scheme.fkColumns = tableForeignKeyMap.get(key);

                tableSchemeMap.put(key, scheme);
            }

            for (String key : parent.keySet()) {
                tableSchemeMap.get(key).parent = tableSchemeMap.get(parent.get(key));
            }
        } catch (Exception e) {
            System.err.println("ERROR: " + e.getMessage());
        }

        return sch;
    }

    public Scheme newScheme(String name, Scheme parent) {
        Scheme temp = new Scheme(name, parent);
        this.tables.add(temp);
        if (parent != null) {
            parent.children.add(name);
        }
        temp.container = this;
        return temp;
    }

    public static Scheme outerUnionScheme(String resultName, List<Scheme> ls) {
        Scheme sResult = null;
        try {
            // Loop through the tables, using the first item on the list as the primary key template.  Ignore foreign keys unless they are consistent.
            if (ls.size() <= 0) throw new OuterUnionException("Cannot make a union of an empty set of tables.");
            sResult = ls.get(0).container.newScheme(resultName, null);
            boolean bFirst = true;
            for (Scheme sTemp : ls) {
                if (bFirst) {
                    // copy the first table over fully
                    for (Column cTemp : sTemp.columns)
                        sResult.newColumn(cTemp.getColumnName(), cTemp.dom, sTemp.pkColumns.contains(cTemp), sTemp.fkColumns.contains(cTemp));

                    bFirst = false;
                    continue;
                }

                // for each column in the table
                // - If it is a primary key column, make sure that it is in the result already
                // - If it is not a primary key column, make sure that it is not a foreign key in the result
                // Also, make sure that all primary key columns in the result exist in the table
                for (Column cTemp : sTemp.columns) {
                    if (sTemp.pkColumns.contains(cTemp)) {
                        // does the key column already exist? If not, retun null
                        boolean bFound = false;
                        for (Column cResultTemp : sResult.pkColumns)
                            if (cTemp.equals(cResultTemp)) {
                                bFound = true;
                                break;
                            }
                        if (bFound) continue;

                        // Key column not found, so return null
                        throw new OuterUnionException("Primary key columns of input tables does not match.");
                    } else {
                        // Non-key column.  Make sure that same column doesn't exist with same name but different domain.
                        boolean bFound = false;
                        for (Column cResultTemp : sResult.columns) {
                            if (cTemp.equals(cResultTemp)) {
                                // the column. If result is fk but the other isn't, remove from the fk list of the result
                                if (sResult.fkColumns.contains(cResultTemp) &&!(sTemp.fkColumns.contains(cTemp)))
                                    sResult.fkColumns.remove(cResultTemp);
                                if (sResult.pkColumns.contains(cResultTemp))
                                    throw new OuterUnionException("Primary key columns of input tables do not match.");
                                bFound = true;
                                break;
                            } else {
                                // Make sure that the column doesn't have the same name but different domain
                                if (cTemp.getColumnName().equals(cResultTemp.getColumnName()) && !(cTemp.dom.equals(cResultTemp.dom)))
                                    throw new OuterUnionException("Column " + cResultTemp.getColumnName() + " with domain " + cResultTemp.dom.toString()
                                            + " is already present with domain " + cTemp.dom.toString() + ".");
                            }
                        }
                        if (bFound) break;

                        // if we get here, then the column needs to be added
                        sResult.newColumn(cTemp.getColumnName(), cTemp.dom, false, sTemp.fkColumns.contains(cTemp));
                    }
                }

                // Finally, check to make sure that the primary key of the result does not strictly subsume the PK of the current table
                for (Column cResultTemp : sResult.pkColumns) {
                    // Does the key column exist? If not, return null
                    boolean bFound = false;
                    for (Column cTemp : sTemp.pkColumns)
                        if (cTemp.equals(cResultTemp)) {
                            bFound = false;
                            break;
                        }
                    if (bFound) break;

                    // key column not found, so turn error
                    throw new OuterUnionException("Primary key columns of input tables do not match.");
                }
            }           
        } catch (OuterUnionException oue) {
            System.err.println("ERROR: " + oue.getMessage());
            System.exit(0);
        }
        return sResult;
    }

    public static Scheme outerUnionSchemeWithProvenance(String resultName, List<Scheme> ls, String provColumnName) {
        Scheme s = Schema.outerUnionScheme(resultName, ls);

        // create a new column that is the provenance column
        List<String> lString = new ArrayList<String>();
        for (Scheme sTemp : ls)
            lString.add(sTemp.getTableName());
        Enumerated e = new Enumerated(lString);
        s.newColumn(provColumnName, e, true, false);

        return s;
    }

    @Override
    public String toString() {
        String temp = "";
        for (Scheme s : tables) {
            temp = temp + s.toString() + System.getProperty("line.separator");
        }
        return temp;
    }

    public void createDB(Connection conn) {
        try {
            Statement stmt = conn.createStatement();
            for (Scheme s : this.tables) {
                stmt.execute(s.toString());
            }
        } catch (SQLException ex) {
            System.err.println("ERROR: " + ex.getMessage());
        }
    }

    public Scheme get(String s) {
        for (Scheme sch : this.tables) {
            if (sch.getTableName().equals(s))
                return sch;
        }
        return null;
    }

    public Transaction getTransaction() {
        Transaction t = new Transaction();
        for (Scheme s : tables)
            s.appendTransaction(t);
        return t;
    }
}
